<?php
class M_accanggaran extends CI_Model{
	
	function save($data,$data_submit){
		$this->db->where($data);
		$this->db->update('t_pengajuan',$data_submit);
		echo 1;
	}
	
	function search($data){
		// $sql = "SELECT pengajuan_id, pengajuan_no_fpd , DATE_FORMAT(pengajuan_date,'%d %M %Y') as pengajuan_date ,pengajuan_sumberdana 
		// , m_anggaran_id,anggaran_kegiatan, pengajuan_keterangan FROM t_pengajuan 
		// LEFT JOIN m_anggaran ON m_anggaran_id = anggaran_id
		// WHERE OLD_PASSWORD(pengajuan_id) =  '".$data."' LIMIT 0 , 1";
		$sql = "SELECT pengajuan_id, pengajuan_no_fpd , DATE_FORMAT(pengajuan_date,'%d %M %Y') as pengajuan_date ,pengajuan_sumberdana 
		, m_anggaran_id,anggaran_kegiatan, pengajuan_keterangan FROM t_pengajuan 
		LEFT JOIN m_anggaran ON m_anggaran_id = anggaran_id
		WHERE pengajuan_no_fpd =  '".$data."' LIMIT 0 , 1";
		$query = $this->db->query($sql);
		$row = $query->row('pengajuan_id');
		$total = $query->num_rows();
		$arr = array();
		foreach($query->result() as $q){
			$arr[] = $q; 
		}
		
		// detail 
		$sql_det = "SELECT t_pengajuandet.pengajuandet_id, t_pengajuandet.m_anggaran_id, pengajuandet_value, 
				   (anggaran_value  * (
							IF(anggaran_vol1 IS NULL, 1,anggaran_vol1) *
							IF(anggaran_vol2 IS NULL, 1,anggaran_vol2) *
							IF(anggaran_vol3 IS NULL, 1,anggaran_vol3) 
					) - IFNULL(val,0)) as sisapagu, 
				   TRIM(
				   CONCAT(
					IF(t_pengajuandet.pengajuandet_vol1 IS NULL,' ' ,t_pengajuandet.pengajuandet_vol1),' ', t_pengajuandet.pengajuandet_sat1, ' ',
					IF(t_pengajuandet.pengajuandet_vol2 IS NULL,' ' ,t_pengajuandet.pengajuandet_vol2),' ',t_pengajuandet.pengajuandet_sat2,' ',
					IF(t_pengajuandet.pengajuandet_vol3 IS NULL,' ' ,t_pengajuandet.pengajuandet_vol3),' ', t_pengajuandet.pengajuandet_sat3,' ',
					IF(t_pengajuandet.pengajuandet_vol4 IS NULL,' ' ,t_pengajuandet.pengajuandet_vol4),' ', t_pengajuandet.pengajuandet_sat4)
				   ) as satuan, 
				   m_anggaran.anggaran_kegiatan, t_pengajuandet.pengajuandet_total FROM (`t_pengajuandet`) LEFT JOIN `v_anggaran_sisapagu` ON `t_pengajuandet`.`m_anggaran_id` = `v_anggaran_sisapagu`.`m_anggaran_id` LEFT JOIN `m_anggaran` ON `t_pengajuandet`.`m_anggaran_id` = `m_anggaran`.`anggaran_id` WHERE `t_pengajuan_id` =  '".$row."'";
		$query_det = $this->db->query($sql_det);
		$data_detail = array();
		foreach($query_det->result() as $det){
			$data_detail[] = array($det->pengajuandet_id , $det->anggaran_kegiatan , $det->pengajuandet_value ,$det->sisapagu,$det->satuan,$det->anggaran_kegiatan,$det->pengajuandet_total);
		}
		
		$final = array(
						'total'   => $total,
						'results' => $arr,
						'detail'  => $data_detail
					  );
		return json_encode($final);
	}
}